﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;

namespace Learun.Util
{
    /// <summary>
    /// 帮助
    /// </summary>
    public class SqliteHelper
    {
        private static string conStr = "Data Source=";

        public static string dbPath = null;
        private static SQLiteConnection conn;
        private static DbTransaction trans;

        /// <summary>
        /// 判断文件是否存在 不存在就新建
        /// </summary>
        /// <returns></returns>
        public static void NewSqliteDatabase(string sqlitedbPath)
        {
            dbPath = sqlitedbPath.Replace('/', '\\');
            conStr = $@"Data Source={dbPath}";
            try
            {
                SQLiteConnection.CreateFile(dbPath);
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        /// 获取指定路径的Sqlite连接
        /// </summary>
        public static void GetConn(string sqlitePath)
        {
            try
            { 
                conn?.Dispose();
                trans?.Dispose();

                conn = new SQLiteConnection();
                conn.ConnectionString = sqlitePath;
                conn.Open();  
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Dispose();
                    trans = null;
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }

        }


        /// <summary>
        /// 开启链接
        /// </summary>
        public static void Start(string ConStr)
        {
            try
            {
                if (conn == null)
                {
                    conn = new SQLiteConnection();
                    conn.ConnectionString = ConStr;
                    conn.Open();
                }

                if (trans == null)
                {
                    trans = conn.BeginTransaction();
                }
            }
            catch (Exception ex)
            {
                if (trans != null)
                {
                    trans.Dispose();
                    trans = null;
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }

        }

        /// <summary>
        /// 提交
        /// </summary>
        public static void SaveChange()
        {
            try
            {
                trans.Commit();
                trans.Dispose();
                trans = null;

                conn.Close();
                conn.Dispose();
                conn = null;

            }
            catch (System.Exception)
            {
                if (trans != null)
                {
                    trans.Dispose();
                    trans = null;
                }

                if (conn != null)
                {
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }
        }


        /// <summary>
        /// 提交
        /// </summary>
        public static void Commit()
        {
            trans.Commit();
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="sqlStr"></param>
        public static int SQLHelper_Add(List<string> sqlStr, List<SQLiteParameter[]> parameters)
        {
            int ret = 0;
            try
            {
                for (int i = 0; i < sqlStr.Count; i++)
                {
                    DbCommand comm = conn.CreateCommand();
                    comm.CommandText = sqlStr[i];
                    comm.Parameters.AddRange(parameters[i]);
                    comm.CommandType = CommandType.Text;
                    ret = comm.ExecuteNonQuery();
                }
            }
            catch (System.Exception)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                    trans = null;
                }

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }

            return ret;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sqlStr"></param>
        public static void SQLHelper_Del(string sqlStr)
        {
            try
            {
                DbCommand comm = conn.CreateCommand();
                comm.CommandText = sqlStr;
                comm.CommandType = CommandType.Text;
                comm.ExecuteNonQuery();
            }
            catch (System.Exception)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                    trans = null;
                }

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }
        }
        public static void Close()
        {
            if (trans != null)
            {
                trans.Rollback();
                trans.Dispose();
                trans = null;
            }

            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int SQLHelper_EF_Add(List<string> sqlStr, List<List<SQLiteParameter>> parameters = null)
        {
            int temp = 0;
            int ret = 0;
            try
            {
                for (int i = 0; i < sqlStr.Count; i++)
                {
                    temp = i;
                    DbCommand comm = conn.CreateCommand();
                    comm.CommandText = sqlStr[i];

                    if (parameters != null && parameters.Count > 0)
                    {
                        List<SQLiteParameter> parametersList = parameters[i];
                        foreach (var item in parametersList)
                        {
                            comm.Parameters.Add(item);
                        }
                    }

                    comm.CommandType = CommandType.Text;
                    ret = comm.ExecuteNonQuery();
                }
            }
            catch (System.Exception ex)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                    trans = null;
                }

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }

            return ret;
        }

        /// <summary>
        /// 获取特定表数据
        /// </summary>
        /// <param name="sql">筛选条件</param>
        /// <returns></returns>
        public static DataTable GetData(string sql)
        {
            DataTable result = null;
            try
            {
                var comm = conn.CreateCommand();
                comm.CommandText = sql;
                comm.CommandType = CommandType.Text;

                DbDataAdapter dda = new SQLiteDataAdapter(comm);
                result = new DataTable();
                dda.Fill(result);
                dda.Dispose();

            }
            catch (System.Exception)
            {
                if (trans != null)
                {
                    trans.Rollback();
                    trans.Dispose();
                    trans = null;
                }
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                    conn = null;
                }
                throw;
            }
            return result;
        }
    }

    public static class HeplerModelDefault
    {
        public static string defaultString { get; set; } = default(string);

        public static char defaultChar { get; set; } = default(char);

        public static int defaultInt { get; set; } = default(int);

        public static double defaultDouble { get; set; } = default(double);

        public static long defaultLong { get; set; } = default(long);

        public static float defaultFloat { get; set; } = default(float);

        public static DateTime defaultDateTime { get; set; } = default(DateTime);
    }

    public class ErrManage
    {
        public List<Exception> ex { get; set; } = new List<Exception>();

        public List<string> exCrux { get; set; } = new List<string>();
    }
}
